IF OBJECT_ID('ssf.ddl_drop_assembly') IS NOT NULL
	DROP PROCEDURE ssf.ddl_drop_assembly
GO

CREATE PROCEDURE ssf.ddl_drop_assembly
	@assembly_name NVARCHAR(128)
	, @db_name NVARCHAR(128) = NULL
AS
/*

*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@sql_error INT
			, @sql_error2 INT
			, @row_count BIGINT
			, @sql VARCHAR(MAX)
			, @nsql NVARCHAR(MAX)
			, @nsql2 NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @two_quotes CHAR(2) = CHAR(39) + CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@exists_fl BIT = NULL
			, @cur_assembly CURSOR
			, @referencing_assembly_name NVARCHAR(128)

DECLARE @referencing_assembly TABLE (assembly_name NVARCHAR(128))
-- ============

-- ============
/*<code_comment><![CDATA[initialize variables]]></code_comment>*/
SET @db_name = ISNULL(@db_name, DB_NAME())
-- ============

-- ============
/*<code_comment><![CDATA[test assembly existence]]></code_comment>*/
EXECUTE @sql_error = ssf.ddl_exists_assembly
	@assembly_name = @assembly_name
	-- NVARCHAR(128)
	, @db_name = @db_name
	-- NVARCHAR(128)
	, @exists_fl = @exists_fl OUTPUT
	-- BIT OUTPUT

IF ISNULL(@sql_error, 1) <> 0
OR @exists_fl IS NULL
BEGIN
	RAISERROR('Failed to test assembly existence: <%s>.', 16, 1, @assembly_name)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[return unless the assembly exists]]></code_comment>*/
IF @exists_fl = 0
	RETURN @@ERROR
-- ============

-- ============
/*<code_comment><![CDATA[drop the referencing assembly]]></code_comment>*/
SET @nsql = 'SELECT DISTINCT ra.name'
	+ @crlf + 'FROM ' + QUOTENAME(@db_name) + '.sys.assemblies a'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.sys.assembly_references ar'
	+ @crlf + 'ON ar.referenced_assembly_id = a.assembly_id'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.sys.assemblies ra'
	+ @crlf + 'ON ra.assembly_id = ar.assembly_id'
	+ @crlf + 'WHERE a.name = ' + @quote + @assembly_name + @quote

PRINT @nsql

INSERT @referencing_assembly
EXECUTE (@nsql)

SET @cur_assembly = CURSOR FOR
SELECT assembly_name
FROM @referencing_assembly

OPEN @cur_assembly

FETCH NEXT FROM @cur_assembly
INTO @referencing_assembly_name

WHILE @@FETCH_STATUS = 0
BEGIN
	EXECUTE ssf.ddl_drop_assembly
		@assembly_name = @referencing_assembly_name
		-- NVARCHAR(128)
		, @db_name = @db_name
		-- NVARCHAR(128)

	FETCH NEXT FROM @cur_assembly
	INTO @referencing_assembly_name
END
-- ============

-- ============
/*<code_comment><![CDATA[build dynamic statement to drop assembly modules]]></code_comment>*/
SET @nsql = 'SET @nsql2 = ' + @quote + @quote
	+ @crlf + ''
	+ @crlf + 'SELECT @nsql2 = @nsql2 + ' + @quote + 'DROP ' + @quote
	+ @crlf + ' + CASE'		-- http://msdn2.microsoft.com/en-us/library/ms190324.aspx
	+ @crlf + @tab + 'WHEN o.type = ' + @quote + 'AF' + @quote + ' THEN ' + @quote + 'AGGREGATE' + @quote
	+ @crlf + @tab + 'WHEN o.type = ' + @quote + 'FS' + @quote + ' THEN ' + @quote + 'FUNCTION' + @quote
	+ @crlf + @tab + 'WHEN o.type = ' + @quote + 'FT' + @quote + ' THEN ' + @quote + 'FUNCTION' + @quote
	+ @crlf + @tab + 'WHEN o.type = ' + @quote + 'PC' + @quote + ' THEN ' + @quote + 'PROCEDURE' + @quote
	+ @crlf + @tab + 'WHEN o.type = ' + @quote + 'TA' + @quote + ' THEN ' + @quote + 'TRIGGER' + @quote
	+ @crlf + 'END + ' + @quote + ' ' + @quote + ' + QUOTENAME(s.[name]) + ' + @quote + '.' + @quote + ' + QUOTENAME(o.[name]) + CHAR(13) + CHAR(10)'
	+ @crlf + 'FROM ' + QUOTENAME(@db_name) + '.sys.assemblies AS a'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.sys.assembly_modules AS am'
	+ @crlf + 'ON am.assembly_id = a.assembly_id'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.sys.objects AS o'
	+ @crlf + 'ON o.[object_id] = am.[object_id]'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.sys.schemas AS s'
	+ @crlf + 'ON s.[schema_id] = o.[schema_id]'
	+ @crlf + 'WHERE a.[name] = ' + QUOTENAME(@assembly_name, @quote)

PRINT @nsql

EXECUTE @sql_error = sp_executesql
	@nsql
	, N'@nsql2 NVARCHAR(MAX) OUTPUT'
	, @nsql2 OUTPUT

IF ISNULL(@sql_error, 1) <> 0
BEGIN
	RAISERROR('Dynamic sql failed: %s.', 16, 1, @nsql)
	RETURN @sql_error
END
-- ============

-- ============
/*<code_comment><![CDATA[drop the assembly modules]]></code_comment>*/
PRINT @nsql2

EXECUTE (@nsql2)

SET @sql_error = @@ERROR

IF ISNULL(@sql_error, 1) <> 0
BEGIN
	RAISERROR('Dynamic sql failed: %s.', 16, 1, @nsql)
	RETURN @sql_error
END
-- ============

-- ============
/*<code_comment><![CDATA[drop the assembly]]></code_comment>*/
SET @nsql = 'DROP ASSEMBLY ' + QUOTENAME(@assembly_name)
SET @nsql2 = 'EXECUTE @sql_error = ' + QUOTENAME(@db_name) + '..sp_executesql'
	+ @crlf + @tab + '@nsql = @nsql'

PRINT @nsql
PRINT @crlf
PRINT @nsql2

EXECUTE @sql_error2 = sp_executesql
	@nsql2
	, N'@nsql NVARCHAR(MAX), @sql_error INT OUTPUT'
	, @nsql
	, @sql_error OUTPUT

IF ISNULL(@sql_error, 1) <> 0
OR ISNULL(@sql_error2, 1) <> 0
BEGIN
	RAISERROR('Dynamic sql failed: %s.', 16, 1, @nsql)
	RETURN @sql_error
END
-- ============

RETURN @@ERROR
GO
